<?php
/**
 * DB的类
 */
class SqlTool {
    public $mysqli;
    //将来这里写死的信息配置到一个文件 host=xx pwd=xxx
    public static $config=array(
        'localhost'=>array(
            'host'=>'localhost',
            'user'=>'root',
            'pwd'=>'mrjin',
            'db'=>'fms',
            'port'=>'3306'
        ),
        'gstj'=>array(
//            'host'=>'localhost',
            'host'=>'172.16.21.19',
            'user'=>'root',
            'pwd'=>'mrjin',
            'db'=>'gstj',
            'port'=>'3306'
        ),
        'travel'=>array(
            'host'=>'localhost',
//            'host'=>'172.16.21.19',
            'user'=>'root',
            'pwd'=>'mrjin',
            'db'=>'travel',
            'port'=>'3306'
        ),
        'gsp'=>array(
            'host'=>'172.17.1.25',
            'user'=>'zhanghuiliang',
            'pwd'=>'UGK@LHog#g45',
            'db'=>'gsp',
            'port'=>'3306'
        ),
        'truck'=>array(
            'host'=>'121.41.6.254',
            'user'=>'root',
            'pwd'=>'KchBb@E%g1xp4kTufxzvgn$X13A8ctBT',
            'db'=>'zngc',
            'port'=>'3306'
        ),
        'gsptest'=>array(
            'host'=>'172.16.1.18',
            'user'=>'ceshi',
            'pwd'=>'ceshi55db605',
            'db'=>'gsp',
            'port'=>'3306'
        ),
        'g7smartv2'=>array(
            'host'=>'localhost',
            'user'=>'root',
            'pwd'=>'mrjin',
            'db'=>'g7smartv2',
            'port'=>'3306'
        )

    );

    //存储结果集,这样会只有一个结果集
    public $array = array();
    //new mysqli($host, $user, $password, $database, $port, $socket)
    /**
     * 初始化方法
     */
    public function __construct($db) {
        //完成初始化任务
        $this->mysqli = new mysqli(self::$config[$db]['host'] ,self::$config[$db]['user'] ,self::$config[$db]['pwd'] ,self::$config[$db]['db'] ,self::$config[$db]['port']);
        if ($this->mysqli->connect_error) {
            die("连接失败" . $this->mysqli->connect_error);
        }
        //设置访问数据库的字符集
        $this->mysqli->query("set names utf8");
    }


    /**
     * @param $query
     * @return 返回二维数组
     */
    public function execute_dql_assoc($query) {
        $array = array();
        $result = $this->mysqli->query($query) or die("查询失败" . $this->mysqli->connect_error);
        //var_dump($query);
        //var_dump($result);
        while ($row = $result->fetch_assoc()) {
            $array[] = $row;
        }
        return $array;
    }

    /**
     * @param $query
     * @return 返回顺序数组
     */
    public function execute_dql_row($query) {
        $array = array();
        $result = $this->mysqli->query($query) or die("查询失败" . $this->mysqli->connect_error);
        while ($row = $result->fetch_row()) {
            $array[] = $row;
        }
        return $array;
    }

    /**
     * @param $query
     * @return 返回array
     */
    public function execute_dql_array($query) {
        $array = array();
        $result = $this->mysqli->query($query) or die("查询失败" . $this->mysqli->connect_error);
        while ($row = $result->fetch_array()) {
            $array[] = $row;
        }
        return $array;
    }

    /**
     * @param $query
     * @return 返回obj数组
     */
    public function execute_dql_object($query) {
        $array = array();
        $result = $this->mysqli->query($query) or die("查询失败" . $this->mysqli->connect_error);
        while ($row = $result->fetch_object()) {
            $array[] = $row;
        }
        return $array;
    }

    /**
     * @param $query
     * @return 返回一个对象
     */
    public function execute_dql_obj($query) {
        $obj = new stdClass();
        $result = $this->mysqli->query($query);// or die("查询失败".$this->mysqli->connect_error);
        //var_dump($query);
        //var_dump($result);
        $row = $result->fetch_assoc();
        //	var_dump($row);
        //return $row;不用返回
        foreach ($row as $key => $val) {
            $obj->$key = $val;
        }
        return $obj;
    }

    /**
     * @param $query
     * @return 结果只有一条,返回一个一维数组
     */
    public function execute_dql_one_array($query) {
        //$array=array();
        $result = $this->mysqli->query($query);// or die("查询失败".$this->mysqli->connect_error);
        //var_dump($query);
        //var_dump($result);
        $row = $result->fetch_assoc();
        //var_dump($row);
        return $row;
    }


    /**
     * @param $query
     * @return 返回一个值
     */
    public function execute_dql_one_row($query) {
        //$array=array();
        $result = $this->mysqli->query($query);// or die("查询失败".$this->mysqli->connect_error);
        //var_dump($query);
        //var_dump($result);
        $row = $result->fetch_row();
        //var_dump($row);
        return $row[0];
    }

    /**
     * @param $query
     * @return 返回一行并转成一个关联数组
     */
    public function execute_dql_one_assoc($query) {
        //$array=array();
        $result = $this->mysqli->query($query);// or die("查询失败".$this->mysqli->connect_error);
        //var_dump($query);
        //var_dump($result);
        $row = $result->fetch_assoc();
        //var_dump($row);
        return $row;
    }

    /**
     * @param $query
     * @return 完成增删改
     */
    public function execute_dml($query) {
        $res = $this->mysqli->query($query);// or die("DML Fail".$this->mysqli->connect_error);
        if (!$res) {
            return 0;//失败
        } else {
            if ($this->mysqli->affected_rows > 0) {
                return 1;//表示成功 或者 返回  $this->mysqli->affected_rows
            } else {
                return 2;//表示没有行数影响
            }
        }
    }

    /**
     * @param $query
     * @return 完成多重增删改
     */
    public function execute_multi_dml($query) {
        $res = $this->mysqli->multi_query($query);// or die("DML Fail".$this->mysqli->connect_error);
        if (!$res) {
            return 0;//失败
        } else {
            if ($this->mysqli->affected_rows > 0) {
                return 1;//表示成功
            } else {
                return 2;//表示没有行数影响
            }
        }
    }


    //返回自增长的id
//    public function getInsertId() {
//        $res = $this->mysqli->query("select last_insert_id() as id");
//        $row = $res->fetch_assoc();
//        return $row['id'];
//    }

    /**
     * @return 返回自增长的id
     */
    public function get_InsertId() {
        return $this->mysqli->insert_id;
    }

    /**
     * @param $table,$array
     * @return 插入表和数组
     */
    public function insertTable($table ,$array) {
        $fields = implode("," ,array_keys($array));
        foreach ($array as &$value) {
            $value = "'" . $value . "'";
        }

        $values = implode("," ,array_values($array));
        $query = "insert into {$table}($fields) values ($values)";
// 				echo $query;
        $this->execute_dml($query);
    }

    //还缺条件
//    public function updateTable($table ,$array) {
//        $query = "update  {$table} set ";
//        foreach ($array as $key => $value) {
//            $query .= "$key='$value',";
//        }
//        substr($query ,-1);
//        echo $query;
//        $this->execute_dml($query);
//        //还要where
//    }

//pageSize: 5, pageNow: 1, pageCount: 0, totalRow: 0
    /**
     * @param $query
     * @param int $pageNow
     * @param int $pageSize
     * @return 分页函数
     */
    function fenye($query,$pageNow=1,$pageSize=5,$sortby="",$desc){
        $query_order = $sortby!=""?" order by $sortby $desc ":"";
        $query1 = $query.$query_order." limit ".$pageSize * ($pageNow - 1).",$pageSize";
        $this->savelog($query1);
        $result = $this->execute_dql_assoc($query1);
        $query2 = preg_replace("/^select .* from/", "select count(*) from", $query); //条件和都可以做活
        $totalRow = (int)$this->execute_dql_one_row($query2);
        $pageCount =ceil($totalRow/$pageSize);
        print_r(json_encode(array("result"=>$result,"totalRow"=>$totalRow,"pageCount"=>$pageCount)));
    }


    /**
     * @param $query
     * @return 记录sql语句
     */
    function savelog($query){
        file_put_contents("query.log", date("Y-m-d H:i:s")."\r\n".$query."\r\n",FILE_APPEND);
    }


    /**
     *
     * @return 页头
     */
    function outputHead(){
        ?>
        <meta name="viewport" content="width=device-width,initial-scale=1.0">
        <link rel="stylesheet" type="text/css" href="../js/jquery-ui.min.css"/>
        <link rel="stylesheet" type="text/css" href="../js/jquery-ui-timepicker-addon.css"/>
        <script type="text/javascript" src="../js/jquery-2.2.2.min.js"></script>
        <script type="text/javascript" src="../js/jquery-ui.min.js"></script>
        <script type="text/javascript" src="../js/jquery-ui-timepicker-addon.js"></script>
        <link rel="stylesheet" href="../css/bootstrap.min.css">
        <script type="text/javascript" src="../js/bootstrap.min.js"></script>
        <link rel="stylesheet" href="../css/bootstrap-select.min.css">
        <script src="../css/bootstrap-select.min.js"></script>
        <?php
    }

    function getDept(){
        $query="select distinct area  from user where area is not null and area !='' group by area";//='智能管车事业部' group by area";// is not null group by area";
        $res = $this->execute_dql_assoc($query);
        foreach($res as $row){
            echo "<option>{$row['area']}</option>";
        }
    }


//下面这个是类的结束符号
}





